﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Npgsql;
using System.Xml;
using System.Data;
using System.Security.Cryptography;
using System.Xml.XPath;
using System.IO;
using DbNetLink.Data;

namespace Projeto.TCC.Dados
{
    class Conexao
    {
        NpgsqlConnection cn = null;

        /// <summary>
        /// ConectionString Atual
        /// </summary>
        public string ConnectionString
        {
            get { return getConnectionString(); }
        }
        /// <summary>
        /// Conexao com o banco
        /// </summary>
        /// <returns></returns>
        private bool conectar()
        {
            cn = new NpgsqlConnection(this.ConnectionString);
            try
            {
                cn.Open();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Erro ao conectar com a base de Dados, " + ex.Message);
            }
        }
        private bool desconectar()
        {
            if (cn.State != System.Data.ConnectionState.Closed)
            {
                cn.Close();
                return true;
            }
            return false;
        }
        /// <summary>
        /// Metodo Insert Generico
        /// </summary>
        /// <param name="p_sql">String com o comando SQL</param>
        /// <param name="p_listParams">Lista com os parametrosContribuinte</param>
        /// <returns></returns>
        public bool insert(string p_sql, List<NpgsqlParameter> p_listParams)
        {
            if (this.conectar())
            {
                NpgsqlCommand cmd = new NpgsqlCommand(p_sql, cn);
                foreach (NpgsqlParameter param in p_listParams)
                {
                    cmd.Parameters.Add(param);
                }
                try
                {
                    int result = cmd.ExecuteNonQuery();
                    return result > 0 ? true : false;
                }
                catch (Exception ex)
                {
                    throw new Exception("Erro ao executar comando no Banco de Dados! " + ex.Message);
                }
                finally
                {
                    this.desconectar();
                }
            }
            return false;
        }

        /// <summary>
        /// Metodo delete Generico
        /// </summary>
        /// <param name="p_sql">String com o comando SQL</param>
        /// <param name="p_listParam">Lista com os parametrosContribuinte</param>
        /// <returns></returns>
        public bool delete(string p_sql, List<NpgsqlParameter> p_listParam)
        {
            if (this.conectar())
            {
                NpgsqlCommand cmd = new NpgsqlCommand(p_sql, cn);
                int result = 0;
                foreach (NpgsqlParameter param in p_listParam)
                {
                    cmd.Parameters.Add(param);
                }
                try
                {
                    result = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception("O registro não pode ser excluido: " + ex.Message);
                }
                finally
                {
                    this.desconectar();
                }
                return Convert.ToBoolean(result);
            }
            return false;
        }
        public bool update(string p_sql, List<NpgsqlParameter> p_listParam)
        {
            if (this.conectar())
            {
                NpgsqlCommand cmd = new NpgsqlCommand(p_sql, cn);
                int result = 0;
                foreach (NpgsqlParameter param in p_listParam)
                {
                    cmd.Parameters.Add(param);
                }
                try
                {
                    result = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception("O registro não pode ser atualizado: " + ex.Message);
                }
                finally
                {
                    this.desconectar();
                }
                return Convert.ToBoolean(result);

            }
            return false;
        }
        /// <summary>
        /// Metodo que retorna uma consulta especifica
        /// </summary>
        /// <param name="p_sql">Comando SQL</param>
        /// <returns>DataTable com resultado</returns>
        public DataTable consultaDt(string p_sql)
        {
            if (this.conectar())
            {
                NpgsqlCommand cmd = new NpgsqlCommand(p_sql, cn);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                try
                {
                    da.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw new Exception("Erro ao executar comando no Banco de Dados! " + ex.Message);
                }
                this.desconectar();
                return dt;
            }
            return null;
        }
        /// <summary>
        /// Metodo que retorna uma consulta especifica
        /// </summary>
        /// <param name="p_sql">Comando SQL</param>
        /// <param name="p_params">Parametros</param>
        /// <returns>DataTable com resultado</returns>
        public DataTable consultaDt(string p_sql, List<NpgsqlParameter> p_params)
        {
            if (this.conectar())
            {
                NpgsqlCommand cmd = new NpgsqlCommand(p_sql, cn);
                foreach (NpgsqlParameter param in p_params)
                {
                    cmd.Parameters.Add(param);

                }
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                try
                {
                    da.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw new Exception("Erro ao executar comando no Banco de Dados! " + ex.Message);
                }
                this.desconectar();
                return dt;
            }
            return null;

        }

        /// <summary>
        /// Metodo Obrigatorio para Login do Usuario no sistema
        /// </summary>
        /// <param name="p_sql">Comando SQL</param>
        /// <param name="list">Parametros</param>
        /// <returns></returns>
        public bool logar(string p_sql, List<NpgsqlParameter> list)
        {
            if (this.conectar())
            {
                bool check = false;
                NpgsqlCommand cmd = new NpgsqlCommand(p_sql, cn);
                foreach (NpgsqlParameter param in list)
                {
                    cmd.Parameters.Add(param);
                }
                try
                {
                    check = Convert.ToBoolean(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw new Exception("Erro ao executar comando no Banco de Dados! " + ex.Message);
                }
                finally
                {
                    this.desconectar();
                }

                return check;
            }
            return false;
        }
        /// <summary>
        /// Obter ConnectionString a partir de um XML
        /// </summary>
        /// <returns>Connection String</returns>
        public string getConnectionString()
        {
            try
            {
                XmlDocument xDoc = new XmlDocument();
                xDoc.Load("C:/Users/Katiane/Desktop/ProjetoJuliTCC/ProjetoConfeitaria/bin/Debug/initConfig.xml");
                XmlNodeList nodeList = xDoc.GetElementsByTagName("connection");
                string server = nodeList[0].ChildNodes[0].InnerText;
                string porta = nodeList[0].ChildNodes[1].InnerText;
                string user = nodeList[0].ChildNodes[2].InnerText;
                string pass = nodeList[0].ChildNodes[3].InnerText;
                string database = nodeList[0].ChildNodes[4].InnerText;

                string conexao = "Server=" + server + ";Port=" + porta + ";User Id=" + user + ";Password=" + pass + ";Database=" + database + "";
                                
                return conexao;
            }
            catch (Exception ex)
            {
                throw new Exception("Erro: " + ex.Message);
            }
        }

        public static string descriptografaSenha(string strCriptografada, string chave)
        {
            try
            {
                TripleDESCryptoServiceProvider objdescriptografaSenha = new TripleDESCryptoServiceProvider();
                MD5CryptoServiceProvider objcriptoMd5 = new MD5CryptoServiceProvider();

                byte[] byteHash, byteBuff;
                string strTempKey = chave;

                byteHash = objcriptoMd5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(strTempKey));
                objcriptoMd5 = null;
                objdescriptografaSenha.Key = byteHash;
                objdescriptografaSenha.Mode = CipherMode.ECB;

                byteBuff = Convert.FromBase64String(strCriptografada);
                string strDecrypted = ASCIIEncoding.ASCII.GetString(objdescriptografaSenha.CreateDecryptor().TransformFinalBlock(byteBuff, 0, byteBuff.Length));
                objdescriptografaSenha = null;

                return strDecrypted;
            }
            catch (Exception ex)
            {
                return "Valores Incorretos." + ex.Message;
            }
        }

        /// <summary>
        /// Metodo que testa a conexao com o banco, e retorna todos os BDs
        /// </summary>
        /// <param name="connectionString">String de Conexao</param>
        /// <returns>Listaa com nome dos Bds</returns>
        public List<string> testConnection(string connectionString)
        {
            NpgsqlConnection cnt;
            try
            {
                cnt = new NpgsqlConnection(connectionString);
                NpgsqlCommand cmd = new NpgsqlCommand("select datname from pg_database where datistemplate = false", cnt);
                cnt.Open();
                List<string> lista = new List<string>();
                NpgsqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string database = dr.GetString(0);
                    lista.Add(database);
                }
                cnt.Close();
                return lista;
            }
            catch (Exception ex)
            {
                throw new Exception("Erro ao conectar no servidor.... " + ex.Message);
            }
        }
        /// <summary>
        /// Executa um comando e retorna apenas um Object
        /// </summary>
        /// <param name="p_sql">Comando SQL</param>
        /// <returns></returns>
        public DataTable executaConsulta(string p_sql, List<NpgsqlParameter> p_params)
        {
            if (this.conectar())
            {
                NpgsqlCommand cmd = new NpgsqlCommand(p_sql, cn);
                foreach (NpgsqlParameter param in p_params)
                {
                    cmd.Parameters.Add(param);
                }
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                try
                {
                    da.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw new Exception("Erro ao executar comando no Banco de Dados! " + ex.Message);
                }
                this.desconectar();
                return dt;
            }
            return null;
        }

        /// <summary>
        /// Executa um comando e retorna apenas um Object
        /// </summary>
        /// <param name="p_sql">Comando SQL</param>
        /// <param name="p_params">Parametro</param>
        /// <returns></returns>
        public object executeScalar(string p_sql, List<NpgsqlParameter> p_params)
        {
            if (this.conectar())
            {
                NpgsqlCommand cmd = new NpgsqlCommand(p_sql, cn);
                foreach (NpgsqlParameter param in p_params)
                {
                    cmd.Parameters.Add(param);
                }

                object obj = cmd.ExecuteScalar();
                this.desconectar();
                return obj;
            }
            return null;
        }

        public object executeScalar(string p_sql)
        {
            if (this.conectar())
            {
                NpgsqlCommand cmd = new NpgsqlCommand(p_sql, cn);
                object obj = cmd.ExecuteScalar();
                this.desconectar();
                return obj;
            }
            return null;
        }

        public bool update(string p_sql)
        {
            if (this.conectar())
            {
                NpgsqlCommand cmd = new NpgsqlCommand(p_sql, cn);
                int result = 0;
                try
                {
                    result = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception("O registro não pode ser atualizado: " + ex.Message);
                }
                finally
                {
                    this.desconectar();
                }
                return Convert.ToBoolean(result);
            }
            return false;
        }
        public bool insert(string p_sql)
        {
            if (this.conectar())
            {
                NpgsqlCommand cmd = new NpgsqlCommand(p_sql, cn);
                int result = 0;
                try
                {
                    result = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception("Erro ao executar comando no Banco de Dados! " + ex.Message);
                }
                finally
                {
                    this.desconectar();
                }
            }
            return false;
        }
    }
}
